import pandas as pd
import matplotlib.pyplot as plt
#1,读excel数据
def read_excel():
    try:
        df = pd.read_excel("score.xlsx",skiprows=2,skipfooter=2)
        return df
    except Exception as e:
        print(f'读取文件失败：{e}')

# 2,成绩进行分段统计 （0-59   2
#                   60-69  10
#                   70-79  15）
def score_counts(scores):
    labels = ['0-59','60-69','70-79','80-89','90-100']
    bins = [0,60,70,80,90,100]
    #分段
    segments = pd.cut(scores, bins, labels=labels)
    #统计
    segment_counts = segments.value_counts().sort_index()
    #打印统计结果
    print(segment_counts)
    return segment_counts

def plot_draw(segment_counts):
    #创建画布
    plt.figure(figsize=[8,8],facecolor='pink')

    #支持中文
    plt.rcParams['font.sans-serif'] = ['simHei']

    #折线图
    plt.subplot(2,2,1)#大小
    plt.plot(segment_counts.index,segment_counts.values)

    #标题
    plt.title('2305班期末成绩统计图（折线图）',fontsize=20)

    # x 轴标签
    plt.xlabel('分段数',fontsize=20)
    plt.ylabel('人数', fontsize=20)

    #显示人数
    for x,y in zip(segment_counts.index,segment_counts.values):
        plt.text(x,y,f'{y}',fontsize=20,color='green')

    #----------------------------------------------------------

    #柱状图
    plt.subplot(2,2,2)
    bars = plt.bar(segment_counts.index,segment_counts.values,color=['red','pink','orange','yellow'])

    for bar in bars:

        bar.get_width()
        plt.text(bar.get_x()+bar.get_width()/2,bar.get_height()+0.5,f'{bar.get_height()}',color='green',fontsize=20)

    plt.title("2305班期末考试统计图（柱状图）",fontsize=20)
    plt.xlabel('分段数',fontsize=20)
    plt.ylabel('人数', fontsize=20)
    plt.grid(True,linestyle='--')

    #---------------------------------------------------------------------

    #饼状图
    plt.subplot(2,2,3)
    plt.pie(segment_counts.values,labels=segment_counts.index,colors=['red','pink','orange','yellow','green'],autopct='%1.2f%%')
    plt.title('2305班期末考试统计图（饼状图）',fontsize=20)

    #调整间距
    plt.subplots_adjust(hspace=0.5)


    #保存图片
    plt.savefig('2305班期末考试统计图')



    plt.show()



if __name__ == '__main__':
    df = read_excel()
    if df is not None and '平均分' in df.columns:
        #分段统计
        segment_counts = score_counts(df['平均分'])
        #绘制图标
        plot_draw(segment_counts)
    else:
        print('数据中没有[平均分]这一列，请检查数据格式')



